package data;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashSet;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;

public class InsertNewBibtex {
	private Connection conn;
	private HashSet<String> bibtexSet = new HashSet<String>();

	public InsertNewBibtex() throws Exception {
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		String url = "jdbc:mysql://localhost:3306/" + "bibsonomy";
		String dbuser = "root";
		String dbpass = "123456";
		Connection con = (Connection) DriverManager.getConnection(url, dbuser,
				dbpass);
		this.conn = con;

	}

	public void extractTestBibtex() throws Exception {
		Statement stmt = null;
		ResultSet rs = null;
		String path = "D:\\CityU\\project\\bibsonomy\\data\\bibtex_test.txt";
		BufferedWriter writer = new BufferedWriter(new FileWriter(path));
		try {
			stmt = (Statement) conn.createStatement();
			rs = (ResultSet) stmt
					.executeQuery("SELECT content_id,journal,booktitle,entrytype,title,author,year from bibtex where (entrytype = 'article' or 'inproceedings') " +
							"and author is not null and author != '' and bibtexAbstract is not null");

			while (rs.next()) {
				String id = rs.getString("content_id");
				/*
				String journal = rs.getString("journal");
				if (journal != null) {
					journal = journal.replaceAll("'", "''");
					journal = journal.replaceAll("\\\\", "\\\\");
				}

				String booktitle = rs.getString("booktitle");
				if (booktitle != null) {
					booktitle = booktitle.replaceAll("'", "''");
					booktitle = booktitle.replaceAll("\\\\", "\\\\");
				}

				String entrytype = rs.getString("entrytype");

				String title = rs.getString("title");
				title = title.replaceAll("'", "''");
				title = title.replaceAll("\\\\", "\\\\");

				String author = rs.getString("author");
				
				author = author.replaceAll("'", "''");
				author = author.replaceAll("\\\\", "\\\\");

				String year = rs.getString("year");
				String sql = "INSERT INTO bibtex_new values ('" + id + "','"
						+ journal + "','" + booktitle + "','" + entrytype
						+ "','" + title + "','" + author + "','" + year + "');";
				writer.write(sql);
				 */
				writer.write(id);
				writer.newLine();
			}

		}

		finally {
			writer.flush();
			writer.close();
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) {
				} // ignore
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) {
				} // ignore
				stmt = null;
			}
		}

	}

	public void reinsertTas() throws Exception {
		readTestBibtexSet();
		Statement stmt = null;
		ResultSet rs = null;
		String path = "D:\\CityU\\project\\bibsonomy\\data\\insert_tasnew.txt";
		BufferedWriter writer = new BufferedWriter(new FileWriter(path));
		try {
			stmt = (Statement) conn.createStatement();
			rs = (ResultSet) stmt
					.executeQuery("SELECT * from tas where content_type = '2'");
			while (rs.next()) {
				String content_id = rs.getString("content_id");
				if (!bibtexSet.contains(content_id))
					continue;
				String user = rs.getString("user");
				String tag = rs.getString("tag");
				if (tag.equalsIgnoreCase("imported"))
					continue;				
				tag = tag.replaceAll("'", "''");
				tag = tag.replace("\\", "\\\\");
				String content_type = rs.getString("content_type");
				String date = rs.getString("date");
				String sql = "INSERT INTO tas_new values ('" + user + "','"
						+ tag + "','" + content_id + "','" + content_type
						+ "','" + date + "');";
				writer.write(sql);
				writer.newLine();
			}

		} finally {
			writer.flush();
			writer.close();
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) {
				} // ignore
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) {
				} // ignore
				stmt = null;
			}
		}

	}

	public void reinsertTas2() throws Exception {
		HashSet<String> existings = readExistings();
		HashSet<String> newSet = findNewInsert(existings);
		System.out.println("existings\t" + existings.size() + "\tnewInsert\t" + newSet.size());
		
		PreparedStatement ps = null;
		try {
			String sql = "INSERT INTO tas_new"
					+ " (user,tag,content_id,content_type,date) values(?,?,?,?,?);";
			ps = conn.prepareStatement(sql);
			int current = 0;
			for(String record : newSet) {
				current++;
				System.out.println(current);
				String[] tokens = record.split("\t");
				String u = tokens[0];
				String tag = tokens[1];
				String c = tokens[2];
				String type = tokens[3];
				String date = tokens[4];				
				ps.setString(1, u);
				ps.setString(2, tag);
				ps.setString(3, c);
				ps.setString(4, type);
				ps.setString(5, date);
				ps.addBatch();
			}
			ps.executeBatch();

		} finally {
			ps.close();
			conn.close();
		}

	}

	private HashSet<String> readExistings() throws Exception {
		HashSet<String> set = new HashSet<String>();
		Statement stmt = null;
		ResultSet rs = null;

		try {
			stmt = (Statement) conn.createStatement();
			rs = (ResultSet) stmt.executeQuery("SELECT * from tas_new ");
			while (rs.next()) {
				String user = rs.getString("user");
				String tag = rs.getString("tag");
				String content_id = rs.getString("content_id");
				String txt = user + "-" + tag + "-" + content_id;
				set.add(txt);
			}

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) {
				} // ignore
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) {
				} // ignore
				stmt = null;
			}
		}
		return set;
	}

	private HashSet<String> findNewInsert(HashSet<String> existings)
			throws Exception {
		readTestBibtexSet();
		Statement stmt = null;
		ResultSet rs = null;
		HashSet<String> newSet = new HashSet<String>();
		try {
			stmt = (Statement) conn.createStatement();
			rs = (ResultSet) stmt
					.executeQuery("SELECT * from tas where content_type = '2'");

			while (rs.next()) {
				String content_id = rs.getString("content_id");
				if (!bibtexSet.contains(content_id))
					continue;
				String user = rs.getString("user");
				String tag = rs.getString("tag");
				if (tag.equalsIgnoreCase("imported"))
					continue;
                if(tag.trim().equalsIgnoreCase(""))
                	continue;
				String txt = user + "-" + tag + "-" + content_id;
				if (existings.contains(txt))
					continue;

				//tag = tag.replaceAll("'", "''");
				//tag = tag.replace("\\", "\\\\");
				String content_type = rs.getString("content_type");
				String date = rs.getString("date");

				String record = user + "\t" + tag + "\t" + content_id + "\t"
						+ content_type + "\t" + date;
				newSet.add(record);
			}

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) {
				} // ignore
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) {
				} // ignore
				stmt = null;
			}
		}
		return newSet;
	}

	private void readTestBibtexSet() throws Exception {
		String path = "D:\\CityU\\project\\bibsonomy\\data\\bibtex_test.txt";
		BufferedReader reader = new BufferedReader(new FileReader(path));
		String line = null;
		while ((line = reader.readLine()) != null) {
			line = line.trim();
			bibtexSet.add(line);
		}
	}
	
	public void readTotal() throws Exception{
		String path = "D:\\CityU\\project\\bibsonomy\\data\\insert_tasnew.txt";
		BufferedReader reader = new BufferedReader(new FileReader(path));
		int current = 0;
		String line = null;
		while((line = reader.readLine())!=null) {
			current ++;
		}
		
		System.out.println("total records\t" + current);
		
	}
	
	public void delete() throws Exception{

		readTestBibtexSet();
		Statement stmt = null;
		ResultSet rs = null;
		String path = "D:\\CityU\\project\\bibsonomy\\data\\delete_tasnew.txt";
		BufferedWriter writer = new BufferedWriter(new FileWriter(path));
		//HashSet<String> deletedSet = new HashSet<String>(); 
		try {
			stmt = (Statement) conn.createStatement();
			rs = (ResultSet) stmt
					.executeQuery("SELECT distinct(content_id) from tas_new");
			while (rs.next()) {
				String content_id = rs.getString("content_id");
				if (bibtexSet.contains(content_id))
					continue;
				
				String sql = "DELETE from tas_new where content_id = '" + content_id +  "';";
				//deletedSet.add(content_id);
				writer.write(sql);
				writer.newLine();
			}

		} finally {
			writer.flush();
			writer.close();
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) {
				} // ignore
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) {
				} // ignore
				stmt = null;
			}
		}

	
		
	}

	public static void main(String[] args) throws Exception {
		InsertNewBibtex exe = new InsertNewBibtex();
		//exe.extractTestBibtex();
		//exe.reinsertTas();
		//exe.reinsertTas2();
		//exe.readTotal();
		exe.delete();
	}

}
